﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmschoolservice : Form
    {
        String TSCH1;
        public frmschoolservice()
        {
            InitializeComponent();
        }

        private void frmschoolservice_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);


            autoid();

            schoolName.Text = "";
            stuHn.Text = "";



            string sqlType2;
            sqlType2 = "select * from doctor ";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType2, Conn);
            da.Fill(ds, "doctor");

            dcName.DataSource = ds.Tables["doctor"];
            dcName.ValueMember = "DCID";
            dcName.DisplayMember = "DCNAM";

            string sqlType3;
            sqlType3 = "select * from school ";
            da = new SqlDataAdapter(sqlType3, Conn);
            da.Fill(ds, "school");

            schoolName.DataSource = ds.Tables["school"];
            schoolName.ValueMember = "SCHID";
            schoolName.DisplayMember = "SCHNAM";
        }

        private void button10_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if ( textBox1.Text!= "" && TSCH1 != "" &&  decs.Text != "" )
            {

            string strdate = Convert.ToString(dateserv.Value.Year) + "/" + Convert.ToString(dateserv.Value.Month) + "/" + Convert.ToString(dateserv.Value.Day);//วันที่

            string query = "insert into school_health(SNO,SCHID,TSCH,DATE,DCID,DIST)values('" + textBox1.Text + "' ,'" + schoolName.SelectedValue + "','" + TSCH1 + "','" + strdate + "','" + dcName.SelectedValue + "','" + decs.Text + "')";
            SqlCommand cmd = new SqlCommand(query, Conn);

            //Use ExecuteNonQuery to insert data.   
            cmd.ExecuteNonQuery();


            string sql1 = "SELECT SNO,SCHNAM,TSCH,DATE,DCNAM,DIST from school health,doctor,school where school_health.SCHID=school.SCHID and doctor.DCID=school_health.DCID and (school_health.SNO)='" + textBox1.Text + "' ";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();
          
            MessageBox.Show("ข้อมูลถูกบันทึกเรียบร้อยแล้ว");
            
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
        }
        private void cleardata1()
        {
            //textBox1.Text = "";
            schoolName.SelectedValue = "";
            //dateserv.Value = "" ;
            TSCH1 = "";
            dcName.SelectedValue = "";
            decs.Text = "";
        }
        private void button8_Click(object sender, EventArgs e)
        {
            cleardata1();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            frmReschool1 frq = new frmReschool1();
            frq.ShowDialog(); 
           
        }

        private void r1_CheckedChanged(object sender, EventArgs e)
        {
            TSCH1 = "บริการฉีดวัคซีน";
        }

        private void r2_CheckedChanged(object sender, EventArgs e)
        {
            TSCH1 = "ตรวจสุขภาพทั่วไป";
        }

        private void button7_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (textBox2.Text != "")
            {
                try
                {

                    string query = "insert into school_service values('" + textBox1.Text + "','" + stuHn.SelectedValue + "' , '" + textBox2.Text + "')";
                    SqlCommand cmd7 = new SqlCommand(query, Conn);
                    cmd7.ExecuteNonQuery();


                    //string sql1 = "SELECT SNO,HN,DISC FROM  school_service where SNO='" + serschoolId.Text + "' ";
                    string sql1 = "SELECT SNO,PNNAM,DISC FROM  school_service,person where school_service.HN = person.HN and SNO='" + textBox1.Text + "' ";
                    SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                    SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                    DataSet ds = new DataSet();


                    odbcDA.Fill(ds);

                    //MessageBox.Show("เพิ่มข้อมูลแล้ว");
                    this.dgvschool.DataSource = ds.Tables[0];
                    DataGridViewCellStyle cs = new DataGridViewCellStyle();
                    cs.Font = new Font("MS Sans Serif", 12, FontStyle.Regular);

                    this.dgvschool.ColumnHeadersDefaultCellStyle = cs;
                    this.dgvschool.Columns[0].HeaderText = "เลขที่การบริการ";
                    //this.dgvschool.Columns[1].HeaderText = "เลขที่ผู้มาใช้บริการ";
                    this.dgvschool.Columns[1].HeaderText = "ชื่อผู้มาใช้บริการ";
                    this.dgvschool.Columns[2].HeaderText = "ผลการตรวจ";

                    this.dgvschool.Columns[0].Width = 150;
                    this.dgvschool.Columns[1].Width = 150;
                    this.dgvschool.Columns[2].Width = 250;
                    //this.dgvschool.Columns[3].Width = 250;
                }
                catch (Exception errToAdd)
                {
                    // showgrid();
                    MessageBox.Show("ข้อมูลผิดพลาดเนื่องจาก" + errToAdd.Message, "ผิดพลาด");
                }
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
            //autoid();
        }
        private void cleardata2()
        {
            //textBox1.Text = "";
            stuHn.SelectedValue = "";
            textBox2.Text = "";
        }
        private void button5_Click(object sender, EventArgs e)
        {
            cleardata2();

        }

        private void button6_Click(object sender, EventArgs e)
        {

            frmperson f1 = new frmperson();
            f1.ShowDialog();
        }

        private void schoolName_SelectedIndexChanged(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            //string sql = "select * from Brandcar,Modelcar,Car,Customer where Brandcar.brand_id=Modelcar.brand_id and Modelcar.model_id=Car.model_id and Car.cust_id=Customer.cust_id and Brandcar.brand_id='" + comboBox2.SelectedValue + "'  ";
            string sql = "select * from person where SCHID = '" + schoolName.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            odbcDA.Fill(ds, "person");

            stuHn.DataSource = ds.Tables["person"];
            stuHn.ValueMember = "HN";
            stuHn.DisplayMember = "PNNAM";
        }
        private void autoid()
        {
            DataTable max = new DataTable();
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT SNO FROM school_health";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            odbcDA.Fill(max);
            DataSet ds = new DataSet();
            odbcDA.Fill(ds, "school_health");
            SqlDataReader reader = cmd1.ExecuteReader();
            int max_id = 0;
            string max_data = "";

            if (max.Rows.Count != 0)
            {
                for (int i = 0; i < max.Rows.Count; i++)
                {
                    max_data = max.Rows[i]["SNO"].ToString();
                    if (max_id < Convert.ToInt32(max_data))
                    {
                        max_id = Convert.ToInt32(max_data);
                    }
                }
                max_id++;
                max_data = Convert.ToString(max_id);
                if (max_data.Length == 1)
                {
                    max_data = "00000" + max_data;
                }
                else if (max_data.Length == 2)
                {
                    max_data = "0000" + max_data;
                }
                else if (max_data.Length == 3)
                {
                    max_data = "000" + max_data;
                }
                else if (max_data.Length == 4)
                {
                    max_data = "00" + max_data;
                }

                else if (max_data.Length == 5)
                {
                    max_data = "0" + max_data;
                }


                textBox1.Text = max_data;
            }
            else
            {
                textBox1.Text = "000001";
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            frmReschool1 fr1 = new frmReschool1();
            fr1.ShowDialog();
        }
    }
    }